package cn.shangqizhenshuai.core;

import cn.shangqizhenshuai.convert.DbType;
import cn.shangqizhenshuai.entity.ColumnEntity;
import cn.shangqizhenshuai.entity.CoreEntity;
import cn.shangqizhenshuai.entity.TableEntity;
import cn.shangqizhenshuai.info.Inputinfo;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 读取数据库来进行数据库信息的提取
 */
public class Core {
    public Logger log = Logger.getLogger(this.getClass());

    private Inputinfo inputinfo =null;
    private DbType dbType = null;

    public void setInputinfo(Inputinfo inputinfo){
    this.inputinfo=inputinfo;
    }
    //根据表名查询字段名称信息
    public List<TableEntity> selecttabcolumn(DruidPooledConnection connection ) throws SQLException {

        DatabaseMetaData md = connection.getMetaData();
        System.out.println(md.getDatabaseProductName());
        System.out.println(md.getDatabaseProductVersion());

        ArrayList<TableEntity> tableEntities = new ArrayList<TableEntity>();
        for (DbType dbType : DbType.values()) {
            if(dbType.getValue().equalsIgnoreCase(inputinfo.getDatatype())){
                this.dbType = dbType;
                break;
            }
        }
    if(dbType!=null){
        String dbastring= null;
        String sql =null;
        if(dbType.getValue().equalsIgnoreCase("mysql")) {
            //查询当前数据库名称
            String datasql = "select database()";
            PreparedStatement temppreparedStatement = connection.prepareStatement(datasql);
            ResultSet tempresultSet = temppreparedStatement.executeQuery();
            tempresultSet.next();
            dbastring = tempresultSet.getString(1);
            tempresultSet.close();
            temppreparedStatement.close();
            sql="select COLUMN_NAME,IS_NULLABLE,DATA_TYPE,COLUMN_KEY from information_schema.COLUMNS where table_name = ? and table_schema = ?";
        }else if(dbType.getValue().equalsIgnoreCase("sql_server")){
            sql = "SELECT col.colorder AS COLUMN_NUMBER ,  col.name AS COLUMN_NAME ,  t.name AS DATA_TYPE ,  col.length AS DATA_SIZE ,  ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS DATA_SIZE_PROPERTY ,  \n" +
                    "        CASE WHEN EXISTS ( SELECT   1  \n" +
                    "                           FROM     dbo.sysindexes si  \n" +
                    "                                    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id  \n" +
                    "                                                              AND si.indid = sik.indid  \n" +
                    "                                    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id  \n" +
                    "                                                              AND sc.colid = sik.colid  \n" +
                    "                                    INNER JOIN dbo.sysobjects so ON so.name = si.name  \n" +
                    "                                                              AND so.xtype = 'PK'  \n" +
                    "                           WHERE    sc.id = col.id  \n" +
                    "                                    AND sc.colid = col.colid ) THEN 'OK'  \n" +
                    "             ELSE ''  \n" +
                    "        END AS COLUMN_KEY ,  \n" +
                    "        CASE WHEN col.isnullable = 1 THEN 'OK'  \n" +
                    "             ELSE ''  \n" +
                    "        END AS IS_NULLABLE \n" +
                    "FROM    dbo.syscolumns col  \n" +
                    "        LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype  \n" +
                    "        inner JOIN dbo.sysobjects obj ON col.id = obj.id  \n" +
                    "                                         AND obj.xtype = 'U'  \n" +
                    "                                         AND obj.status >= 0  \n" +
                    "        LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id  \n" +
                    "        LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id  \n" +
                    "                                                      AND col.colid = ep.minor_id  \n" +
                    "                                                      AND ep.name = 'MS_Description'  \n" +
                    "        LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id  \n" +
                    "                                                         AND epTwo.minor_id = 0  \n" +
                    "                                                         AND epTwo.name = 'MS_Description'  \n" +
                    "WHERE   obj.name = ? \n" +
                    "ORDER BY col.colorder ";
        }

        //执行sql
        log.debug("开始查询数据库中对应的表");
        for (String s : inputinfo.getTabname()) {
            PreparedStatement pstmt  = connection.prepareStatement(sql);
            pstmt.setString(1,s);
            if(dbastring!=null){
                pstmt.setString(2,dbastring);
            }
            ResultSet resultSet = pstmt.executeQuery();
            //遍历结果集填充到对象中
            TableEntity tableEntity = new TableEntity();
            tableEntity.setTabname(s);
            tableEntity.setTabtype("mysql");
            ArrayList<ColumnEntity> columnEntities = new ArrayList<ColumnEntity>();
            while (resultSet.next()){
                ColumnEntity columnEntity = new ColumnEntity();
                columnEntity.setCOLUMN_NAME(resultSet.getString("COLUMN_NAME"));
                if ("PRI".equalsIgnoreCase(resultSet.getString("COLUMN_KEY"))) {
                    columnEntity.setCOLUMN_KEY(true);
                } else {
                    columnEntity.setCOLUMN_KEY(false);
                }

                columnEntity.setDATA_TYPE(resultSet.getString("DATA_TYPE"));
                if ("YES".equalsIgnoreCase(resultSet.getString("IS_NULLABLE"))) {
                    columnEntity.setIS_NULLABLE(true);
                } else {
                    columnEntity.setIS_NULLABLE(false);
                }
                columnEntities.add(columnEntity);
            }
            resultSet.close();
            tableEntity.setColumnEntitys(columnEntities);
            tableEntities.add(tableEntity);
            pstmt.close();
        }

        return tableEntities;

    }else{
        log.debug("不支持此类型数据库");
        new  Exception("不支持此类型数据库");
        return null;
    }



        }



}
